package common.main.tianYanCha;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

public class TianYanChaSaveDataToSql {


    public static String saveSqlModel = "insert into company_info " +
            "(company_name,company_financing,company_founding_time,company_place,company_product_introduction,company_keyword,company_url,parent_company_name,core_members) " +
            "values" +
            "('<company_name>','<company_financing>','<company_founding_time>','<company_place>','<company_product_introduction>'," +
            "'<company_keyword>','<company_url>','<parent_company_name>','<core_members>')";

    private static Connection conn = null;
    private static Statement stmt = null;

    private static void connectDatabase() throws ClassNotFoundException, IllegalAccessException, InstantiationException, SQLException {
        Class.forName("com.mysql.jdbc.Driver").newInstance();
        String databaseHost = TianYanChaConfig.mysqlHost;//
        String databaseName = TianYanChaConfig.mysqlDatabaseName;// 已经在MySQL数据库中创建好的数据库。
        String userName = TianYanChaConfig.mysqlUserName;// MySQL默认的root账户名
        String password = TianYanChaConfig.mysqlPassword;// 默认的root账户密码为空
        String url ="jdbc:mysql://"+databaseHost+"/"
                +databaseName+"?user="+userName+"&password="+password+"&useUnicode=true&characterEncoding=UTF-8";
        if(conn == null || conn.isClosed()){
            conn = DriverManager.getConnection(url);
        }
        if(stmt == null || stmt.isClosed()){
            stmt = conn.createStatement();
        }
    }


    public static void saveDataToSql(List<TianYanChaData> companyList){
        try {
            connectDatabase();

            for(int i = 0;i < companyList.size();i++){
                TianYanChaData company = companyList.get(i);
                String sql = saveSqlModel.replace("<company_name>",company.getCompany_name()==null?"":company.getCompany_name().replace("'","\\'"))
                        .replace("<company_financing>",company.getCompany_financing()==null?"":company.getCompany_financing().replace("'","\\'"))
                        .replace("<company_founding_time>",company.getCompany_founding_time()==null?"":company.getCompany_founding_time().replace("'","\\'"))
                        .replace("<company_place>",company.getCompany_place()==null?"":company.getCompany_place().replace("'","\\'"))
                        .replace("<company_product_introduction>",company.getCompany_product_introduction()==null?"":company.getCompany_product_introduction().replace("'","\\'"))
                        .replace("<company_keyword>",company.getCompany_keyword()==null?"":company.getCompany_keyword().replace("'","\\'"))
                        .replace("<company_url>",company.getCompany_url()==null?"":company.getCompany_url().replace("'","\\'"))
                        .replace("<parent_company_name>",company.getParent_company_name()==null?"":company.getParent_company_name().replace("'","\\'"))
                        .replace("<core_members>",company.getCore_members()==null?"":company.getCore_members().replace("'","\\'"));
                try{
                    stmt.executeUpdate(sql);
                }catch (Exception e){
                    e.printStackTrace();
                }
            }

            stmt.close();
            conn.close();

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
